package jdbc.homework02;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jdbc.homework01.DbUtil;

public class StudentDao {

	// 查询所有学生信息
	public static List<Student> findAll() {
		List<Student> list = null;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_student";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			list = new ArrayList<Student>();
			while (rs.next()) {
				long stuno = rs.getLong(1);
				String sname = rs.getString(2);
				String sex = rs.getString(3);
				String tel = rs.getString(4);
				String qq = rs.getString(5);
				String wechat = rs.getString(6);
				long gradeid = rs.getLong(7);
				Date enterdate = rs.getDate(8);
				String fromcity = rs.getString(9);
				Date brithday = rs.getDate(10);
				String address = rs.getString(11);
				String pass = rs.getString(12);
				Student st = new Student(stuno, sname, sex, tel, qq, wechat, gradeid, enterdate, fromcity, brithday,
						address, pass);

				list.add(st);
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return null;
	}

	// 增加新学生
	public static int addStudent(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "insert into s_student(sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass) values(?,?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSname());
			ps.setString(2, st.getSex());
			ps.setString(3, st.getTel());
			ps.setString(4, st.getQq());
			ps.setString(5, st.getWechat());
			ps.setLong(6, st.getGradeid());
			ps.setDate(7, st.getEnterdate());
			ps.setString(8, st.getFromcity());
			ps.setDate(9, st.getBrithday());
			ps.setString(10, st.getAddress());
			ps.setString(11, st.getPass());

			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return 0;
	}

	// 修改学生信息
	public static int update(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "update s_student set sname=?,sex=? where stuno=?";

		try {
			ps = con.prepareStatement(sql);
			ps.setString(1, st.getSname());
			ps.setString(2, st.getSex());
			ps.setLong(3, st.getStuno());

			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {

			e.printStackTrace();
		}
		return 0;
	}

	// 删除学生
	public static int delete(Student st) {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "delete from s_student  where stuno=?";
		try {
			ps = con.prepareStatement(sql);
			ps.setLong(1, st.getStuno());
			int r = ps.executeUpdate();
			return r;
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return 0;
	}

	// 分页查询

	// pageNo 第几页
	// pagesize 一页多少条
	public static List<Student> limiteSelect(int pageNo, int pagesize) {
		List<Student> list = null;
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select * from s_student limit ?,?";
		try {
			ps = con.prepareStatement(sql);
			ps.setInt(1, (pageNo - 1) * pagesize);
			ps.setInt(2, pagesize);
			rs = ps.executeQuery();
			list = new ArrayList<Student>();
			while (rs.next()) {
				long stuno = rs.getLong(1);
				String sname = rs.getString(2);
				String sex = rs.getString(3);
				String tel = rs.getString(4);
				String qq = rs.getString(5);
				String wechat = rs.getString(6);
				long gradeid = rs.getLong(7);
				Date enterdate = rs.getDate(8);
				String fromcity = rs.getString(9);
				Date brithday = rs.getDate(10);
				String address = rs.getString(11);
				String pass = rs.getString(12);
				Student st = new Student(stuno, sname, sex, tel, qq, wechat, gradeid, enterdate, fromcity, brithday,
						address, pass);

				list.add(st);
			}
			return list;
		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}

		return null;
	}

	// 分组查询
	public static Map<String, String> groupBy() {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "select sex , count(*) as num from s_student group by sex";
		Map<String, String> map = new HashMap<String, String>();

		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				// System.out.println(rs.getString("sex") + " " + rs.getInt("num"));
				map.put(rs.getString("sex"), rs.getInt("num") + "");

			}
			return map;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtil.close(rs, null, ps, con);
		}
		return null;
	}

	// 统计人数
	public static int count() {
		Connection con = DbUtil.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		int num = 0;
		String sql = "select count(*) from s_student ";
		try {
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
			return num;
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return num;
	}
}
